package com.metacube.questionbank.dao;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;

import com.metacube.questionbank.jdbc.QuestionRowMapper;
import com.metacube.questionbank.model.Question;

/*
 * @author Team Neeraj Jain
 * */

public class QuestionDAO {
	@Autowired
	DataSource dataSource;

	/*
	 * This method returns all the questions that are asked by any user.
	 * 
	 * @return list of questions
	 */
	public List<Question> getQuestions() {
		List<Question> qUList = new ArrayList<Question>();
		String sql = "select * from questions order by ques_date desc";
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		try {
			qUList = jdbcTemplate.query(sql, new QuestionRowMapper());
		} catch (Exception e) {
			System.out.println("");
		}
		return qUList;
	}

	/*
	 * @param userId id of user This method returns a list of questions that are
	 * posted by a particular user.
	 * 
	 * @return list of questions
	 */
	public List<Question> getQuestionsGivenByUser(int userId) {
		List<Question> question = new ArrayList<Question>();
		String sql = "select * from questions where user_id=" + userId;
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		question = jdbcTemplate.query(sql, new QuestionRowMapper());
		return question;
	}

	/*
	 * @param qId id of question This method returns the question by its id.
	 * 
	 * @return question as per the id.
	 */
	public Question getQuestionByQId(int qId) {
		Question objQuestion;
		String sql = "select * from questions where ques_id=" + qId;
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		objQuestion = jdbcTemplate.queryForObject(sql, new QuestionRowMapper());
		return objQuestion;
	}

	/*
	 * @param objQuestion object of Question class This method inserts question
	 * in the database.
	 */
	public void insertQuestion(Question questionObj) {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
		Calendar cal = Calendar.getInstance();
		cal.setTime(new Date());
		cal.add(Calendar.DATE, 15);
		String closingDate = sdf.format(cal.getTime());
		String sql = "INSERT INTO questions (ques_title, ques_desc,user_id,closing_date,isclosed) VALUES (?,?,?,?,?)";
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		jdbcTemplate.update(sql, new Object[] { questionObj.getQuestionTitle(),
				questionObj.getQuestionDesc(), questionObj.getUserId(),
				closingDate, questionObj.getClosingStatus() });
	}

	/*
	 * @param query query typed by user.
	 * 
	 * @param relatedQuestions current list of related questions .This method
	 * returns a list of related questions on the basis of query written by
	 * user.This method finds the tags in the query and finds the questions that
	 * are related to those tags.
	 * 
	 * @return list of related questions.
	 */
	public List<Question> getRelatedQuestionById(String string,
			List<Question> relatedQuestion) {
		String sql = "select ques_id,ques_title,ques_desc,user_id,ques_date,closing_date,isclosed from questiontags natural join tags natural join questions where tag_name=\""
				+ string + "\"";
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		boolean isPresent = true;
		List<Question> qUList = jdbcTemplate
				.query(sql, new QuestionRowMapper());
		for (int i = 0; i < qUList.size(); i++) {
			System.out.println(qUList.get(i).getQuesId());
		}
		if (relatedQuestion.size() == 0) {
			relatedQuestion = qUList;
		} else {
			for (int tagIndex = 0; tagIndex < qUList.size(); tagIndex++) {
				isPresent = true;

				for (int quesIndex = 0; quesIndex < relatedQuestion.size(); quesIndex++) {
					if (qUList.get(tagIndex).getQuesId() == relatedQuestion
							.get(quesIndex).getQuesId()) {
						isPresent = false;
						break;
					} else {
						continue;
					}
				}
				if (isPresent) {
					relatedQuestion.add(qUList.get(tagIndex));
				}
			}
		}
		return relatedQuestion;
	}

	/*
	 * This method returns the maximum question id from the database
	 * 
	 * @return maxQId
	 */
	public int getMaxQId() {
		int maxID = 0;
		String sql = "select MAX(ques_id) from questions";
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		maxID = jdbcTemplate.queryForInt(sql);
		return maxID;
	}

	/*
	 * This method returns a list of questions which are unanswered.
	 * 
	 * @return list of questions.
	 */
	public List<Question> getUnAnsweredQuestions() {
		List<Question> unAnswered = new ArrayList<Question>();
		String sql = "select * from questions where ques_id not in (select ques_id from answers)";
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		unAnswered = jdbcTemplate.query(sql, new QuestionRowMapper());
		return unAnswered;

	}
}
